﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using Microsoft.VisualBasic.Devices;
using System.Runtime.InteropServices;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.EditorInput;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using myDBDataAdapter = System.Data.SqlClient.SqlDataAdapter;
using myDBConnection = System.Data.SqlClient.SqlConnection;
using myDBTransaction = System.Data.SqlClient.SqlTransaction;
using myDBCommand = System.Data.SqlClient.SqlCommand;
using cadApp = Autodesk.AutoCAD.ApplicationServices.Application;
using System.Windows.Forms;
using YNCNumberSys.db;
using YNCNumberSys.ZJClass;
using YNCNumberSys.DbDal;
/// <summary>
/// 20221118日，发布给唐霞一个版本。不确定改的版本是否是最终版，测试使用，计划下周跟产。
/// bin文件夹下的 YNCNumberSys.bundle\Contents复制到Autodesk\ApplicationPlugins下，启动AutoCAD可以自动加载。
/// </summary>
namespace YNCNumberSys
{
    public class YNCNumberMethod
    {
        [DllImport("kernel32.dll")]
        private static extern IntPtr _lopen(string lpPathName, int iReadWrite);

        [DllImport("kernel32.dll")]
        private static extern bool CloseHandle(IntPtr hObject);

        private const int OF_READWRITE = 2;

        private const int OF_SHARE_DENY_NONE = 0x40;

        private static readonly IntPtr HFILE_ERROR = new IntPtr(-1);

        /// <summary>
        /// 数据库连接和访问的通用方法封装。具体的SQL文封装在代码中，没有单独列出。
        /// </summary>
        static CommandManager commandManager = new CommandManager();
        static ConnectionManager connectionManager = new ConnectionManager();
        /// <summary>
        /// 文件是否打开，如果未打开返回-1；如果文件打开，关闭，并返回0。XSL ADD。
        /// </summary>
        /// <param name="fileFullName">文件全路径。</param>
        /// <returns></returns>
        public static int FileIsOpen(string fileFullName)
        {
            if (!File.Exists(fileFullName))
            {
                return -1;
            }
            IntPtr handle = _lopen(fileFullName, OF_READWRITE | OF_SHARE_DENY_NONE);
            if (handle == HFILE_ERROR)
            {
                return 1;
            }
            CloseHandle(handle);
            return 0;
        }

        /// <summary>
        /// 获取文件夹下的文件信息表（内存信息表：包含的信息图纸序号、图纸大小、
        /// 图纸类型、原文件名、扩展名），具体用途不详。
        /// </summary>
        /// <param name="files">文件</param>
        /// <returns>文件信息表（内存）</returns>
        public static DataTable GetFileInfo(FileInfo[] files,bool includeZP)
        {
            DataTable m_DataTable;
            m_DataTable = new DataTable();
            m_DataTable.Columns.Add("图纸序号");
            m_DataTable.Columns.Add("图纸大小");
            m_DataTable.Columns.Add("图纸类型");
            m_DataTable.Columns.Add("原文件名");
            m_DataTable.Columns.Add("扩展名");
            DataRow dr;
            int n = files.Length;
            List<string> list_序号 = new List<string>();
            for (int i = 0; i < n; i++)
            {
                if (files[i].Name.Substring(2, 1) == "-")
                {

                    if (files[i].Extension.ToLower() == ".dwg" || files[i].Extension.ToLower() == ".kmg")
                    {
                        int result = FileIsOpen(files[i].Name);
                        if (result != 1)
                        {
                            dr = m_DataTable.NewRow();
                            string onlyFileName = System.IO.Path.GetFileNameWithoutExtension(files[i].Name);
                            dr["原文件名"] = onlyFileName;
                            dr["扩展名"] = files[i].Extension;
                            dr["图纸大小"] = files[i].Name.Substring(0, 1);
                            dr["图纸类型"] = files[i].Name.Substring(1, 1);
                            dr["图纸序号"] = onlyFileName.Remove(0, 3).ToUpper();
                            string str = onlyFileName.Remove(0, 3).ToUpper();
                            Regex objNotNumberPattern = new Regex("[^0-9,]");
                            if (objNotNumberPattern.IsMatch(str))
                            {
                                if (str=="ZP")
                                {
                                    if (list_序号.IndexOf(str) < 0)
                                    {
                                        if (includeZP)
                                        {
                                            list_序号.Add(str);
                                            m_DataTable.Rows.Add(dr);
                                        }
                                    }
                                    else
                                    {
                                        Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog("序号"+str + "重复！");
                                    }
                                }
                                else
                                {
                                    Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(str+"文件名不合法！");
                                }
                            }
                            else
                            {
                                if (str.IndexOf(",") > 0)
                                {
                                    string[] strs = str.Split(',');
                                    bool b_find1 = false;
                                    bool b_find重复 = false;
                                    for (int ii = 0; ii < strs.Length; ii++)
                                    {
                                        if (strs[ii] == "1")
                                        {
                                            b_find1 = true;
                                        }
                                        if (list_序号.IndexOf(strs[ii]) < 0)
                                        {
                                            list_序号.Add(strs[ii]);
                                        } 
                                        else
                                        {
                                            b_find重复 = true;
                                            break;
                                        }
                                    }
                                    if (b_find1 != true && b_find重复!=true)
                                    {
                                        m_DataTable.Rows.Add(dr);
                                    }
                                    else
                                    {
                                        if (b_find1)
                                        {
                                            Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(onlyFileName + "文件名不合法，不能有1号件！");
                                        }
                                        else
                                        {
                                            Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog("序号" + str + "重复！");
                                        }
                                    }
                                }
                                else
                                {
                                    if (str!="1")
                                    {
                                        if (list_序号.IndexOf(str) < 0)
                                        {
                                            list_序号.Add(str);
                                            m_DataTable.Rows.Add(dr);
                                        }
                                        else
                                        {
                                            Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog("序号" + str + "重复！");
                                        }
                                    }
                                    else
                                    {
                                        Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(onlyFileName+"文件名不合法，不能有1号件！");
                                    }
                                }
                            }
                        }
                        else
                        {
                            string fileName = System.IO.Path.GetFileName(files[i].Name);
                            Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(fileName + "不能被处理，请关闭后重试!");
                        }
                    }
                }
            }
            list_序号.Clear();
            return m_DataTable;
        }
        /// <summary>
        /// 要号？conn，trans是传入的，不需要进行处理。
        /// </summary>
        /// <param name="dt_文件夹内文件信息"></param>
        /// <param name="str_机床型号"></param>
        /// <param name="str_A号"></param>
        /// <param name="str_申请人"></param>
        /// <param name="str_审核人"></param>
        /// <param name="str_申请日期"></param>
        /// <param name="i_编码位数"></param>
        /// <returns></returns>
        public static DataTable DoNumberForApplyNumber(
            myDBConnection conn,
            myDBTransaction trans,
            DataTable dt_文件夹内文件信息, 
            string str_机床型号, 
            string str_A号, 
            string str_申请人, 
            string str_审核人, 
            string str_申请日期,
            int i_编码位数)
        {
            
            DataTable dt_return;
            dt_return = dt_文件夹内文件信息.Copy();
            dt_return.Columns.Add("图纸编号");

            DataView dataView = dt_文件夹内文件信息.DefaultView;
            DataTable dt_distinctForType = dataView.ToTable(true, "图纸类型");//J，D，L，Z，4C，？？？
            DataTable dt_distinctForSize = dataView.ToTable(true, "图纸大小");//0，1，2，3，4

            string sql = string.Empty;
            //myDBConnection cnn =null;
            //myDBTransaction trans =null;
            try
            {
                //cnn=connectionManager.getConnection();
                //if (cnn.State != ConnectionState.Open)
                //{
                //    cnn.Open();
                //}
                //trans=  cnn.BeginTransaction();
                sql = "select * from 图纸编号表 where 1=1";
                DataTable dt_图纸编号表 = commandManager.ExecuteNonQuery(conn, trans, sql, "图纸编号表");
                sql = "select * from 初始编码 where 1=1";
                DataTable dt_初始编码 = commandManager.ExecuteNonQuery(conn, trans, sql, "初始编码");
                //判断图纸编号表是否为空？
                #region 图纸编号表为空
                if (dt_图纸编号表.Rows.Count == 0)
                {
                    //图纸编号表为空
                    foreach (DataRow dr_图纸大小 in dt_distinctForSize.Rows)
                    {
                        int firstNumber = 0;
                        string str_图纸类型 = dr_图纸大小["图纸大小"].ToString() + dt_distinctForType.Rows[0]["图纸类型"].ToString();
                        DataRow[] drs_图纸编号表 = dt_图纸编号表.Select("图纸类型='" + str_图纸类型 + "'", "ID ASC");//2019-1-30
                        if (drs_图纸编号表.Length == 0)
                        {
                            firstNumber = int.Parse(dt_初始编码.Rows[0][str_图纸类型].ToString());
                        }
                        else
                        {
                            firstNumber = int.Parse(drs_图纸编号表[drs_图纸编号表.Length - 1]["图纸编号"].ToString());
                        }
                        //firstNumber = int.Parse(dt_初始编码.Rows[0][str_图纸类型].ToString());
                        for (int ir = 0; ir < dt_文件夹内文件信息.Rows.Count; ir++)
                        {
                            if (dt_文件夹内文件信息.Rows[ir]["图纸大小"].ToString() == dr_图纸大小["图纸大小"].ToString())
                            {
                                if (ir == 0)
                                {
                                    //先处理ZP
                                    DataRow[] drs_ZP = dt_文件夹内文件信息.Select("图纸序号='ZP'");
                                    if (drs_ZP.Length == 1)
                                    {
                                        //先处理ZP
                                        string str_图纸类型1 = drs_ZP[0]["图纸大小"].ToString() + dt_distinctForType.Rows[0]["图纸类型"].ToString();//2019-1-30
                                        int firstNumber1 = 0;//2019-1-30
                                        if (drs_ZP[0]["图纸大小"].ToString() == dr_图纸大小["图纸大小"].ToString())
                                        {
                                            firstNumber++;
                                            firstNumber1 = firstNumber;
                                        }
                                        else
                                        {
                                            DataRow[] drs_图纸编号表1 = dt_图纸编号表.Select("图纸类型='" + str_图纸类型1 + "'", "ID ASC");
                                            if (drs_图纸编号表1.Length == 0)
                                            {
                                                firstNumber1 = int.Parse(dt_初始编码.Rows[0][str_图纸类型1].ToString());
                                            }
                                            else
                                            {
                                                firstNumber1 = int.Parse(drs_图纸编号表1[drs_图纸编号表1.Length - 1]["图纸编号"].ToString());
                                            }
                                            firstNumber1++;
                                        }
                                        sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                        "values('" + str_机床型号 + "','" + str_A号 + "','" + drs_ZP[0]["图纸序号"].ToString() + "','"
                                        + firstNumber1.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                        + 0 + ",'" + str_图纸类型1 + "')";
                                        commandManager.update(conn,trans, sql);
                                        DataRow[] drs_RTZP = dt_return.Select("图纸序号='ZP'");
                                        drs_RTZP[0]["图纸编号"] = firstNumber1;
                                        drs_RTZP[0]["图纸编号"] = str_图纸类型1 + firstNumber1.ToString().PadLeft(i_编码位数, '0');
                                    }
                                    if (dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() != "ZP")
                                    {
                                        //不再处理ZP了
                                        firstNumber++;
                                        sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                        "values('" + str_机床型号 + "','" + str_A号 + "','" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "','"
                                        + firstNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                        + 0 + ",'" + str_图纸类型 + "')";
                                        commandManager.update(conn,trans, sql);
                                        dt_return.Rows[ir]["图纸编号"] = firstNumber;
                                        dt_return.Rows[ir]["图纸编号"] = str_图纸类型 + firstNumber.ToString().PadLeft(i_编码位数, '0');
                                    }
                                }
                                else
                                {
                                    //不再处理ZP
                                    if (dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() != "ZP")
                                    {
                                        //MessageBox.Show("不再处理ZP了");
                                        //不再处理ZP了
                                        firstNumber++;
                                        sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                        "values('" + str_机床型号 + "','" + str_A号 + "','" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "','"
                                        + firstNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                        + 0 + ",'" + str_图纸类型 + "')";
                                        commandManager.update(conn,trans, sql);
                                        dt_return.Rows[ir]["图纸编号"] = firstNumber;
                                        dt_return.Rows[ir]["图纸编号"] = str_图纸类型 + firstNumber.ToString().PadLeft(i_编码位数, '0');
                                    }
                                }
                            }
                        }
                    }
                }
                #endregion
                #region 图纸编号表不为空
                else
                {
                    //图纸编号表不为空
                    foreach (DataRow dr_图纸大小 in dt_distinctForSize.Rows)
                    {
                        int firstNumber = 0;
                        string str_图纸类型 = dr_图纸大小["图纸大小"].ToString() + dt_distinctForType.Rows[0]["图纸类型"].ToString();
                        DataRow[] drs_图纸编号表 = dt_图纸编号表.Select("图纸类型='" + str_图纸类型 + "'", "ID ASC");
                        if (drs_图纸编号表.Length == 0)
                        {
                            firstNumber = int.Parse(dt_初始编码.Rows[0][str_图纸类型].ToString());
                        }
                        else
                        {
                            firstNumber = int.Parse(drs_图纸编号表[drs_图纸编号表.Length - 1]["图纸编号"].ToString());
                        }

                        for (int ir = 0; ir < dt_文件夹内文件信息.Rows.Count; ir++)
                        {
                            if (dt_文件夹内文件信息.Rows[ir]["图纸大小"].ToString() == dr_图纸大小["图纸大小"].ToString())
                            {
                                if (ir == 0)
                                {
                                    //先处理ZP
                                    DataRow[] drs_ZP = dt_文件夹内文件信息.Select("图纸序号='ZP'");
                                    if (drs_ZP.Length == 1)
                                    {
                                        string str_图纸类型1 = drs_ZP[0]["图纸大小"].ToString() + dt_distinctForType.Rows[0]["图纸类型"].ToString();//2019-1-30
                                        //有无空号？
                                        sql = "select * from 图纸编号表 where 作废 = 1";
                                        DataTable dt_空号 = commandManager.ExecuteNonQuery(conn,trans, sql, "图纸编号表");

                                        DataRow[] drs_空号 = dt_空号.Select("图纸类型='" + str_图纸类型1 + "' and 作废=" + 1, "ID ASC");
                                        //判断是否有空号？
                                        if (drs_空号.Length > 0)
                                        {
                                            DataRow dr_首空 = drs_空号[0];
                                            //先处理ZP
                                            sql = "update 图纸编号表 set 机床型号 = '" + str_机床型号
                                                + "',A号= '" + str_A号
                                                + "',零件序号= '" + drs_ZP[0]["图纸序号"].ToString()
                                                + "',申请人= '" + str_申请人
                                                + "',审核人= '" + str_审核人
                                                + "',申请日期= '" + str_申请日期
                                                + "',作废= " + 0
                                                + " where ID=" + int.Parse(dr_首空["ID"].ToString());
                                            commandManager.update(conn,trans, sql);
                                            DataRow[] drs_RTZP = dt_return.Select("图纸序号='ZP'");
                                            drs_RTZP[0]["图纸编号"] = dr_首空["图纸编号"];
                                            drs_RTZP[0]["图纸编号"] = str_图纸类型1 + int.Parse(dr_首空["图纸编号"].ToString()).ToString().PadLeft(i_编码位数, '0');
                                        }
                                        else
                                        {
                                            //先处理ZP
                                            int firstNumber1=0;//2019-1-30
                                            if (drs_ZP[0]["图纸大小"].ToString() == dr_图纸大小["图纸大小"].ToString())
                                            {
                                                firstNumber++;
                                                firstNumber1 = firstNumber;
                                            } 
                                            else
                                            {
                                                DataRow[] drs_图纸编号表1 = dt_图纸编号表.Select("图纸类型='" + str_图纸类型1 + "'", "ID ASC");
                                                if (drs_图纸编号表1.Length == 0)
                                                {
                                                    firstNumber1 = int.Parse(dt_初始编码.Rows[0][str_图纸类型1].ToString());
                                                }
                                                else
                                                {
                                                    firstNumber1 = int.Parse(drs_图纸编号表1[drs_图纸编号表1.Length - 1]["图纸编号"].ToString());
                                                }
                                                firstNumber1++;
                                            }
                                            
                                            sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                            "values('" + str_机床型号 + "','" + str_A号 + "','" + drs_ZP[0]["图纸序号"].ToString() + "','"
                                            + firstNumber1.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                            + 0 + ",'" + str_图纸类型1 + "')";
                                            commandManager.update(conn,trans, sql);
                                            DataRow[] drs_RTZP = dt_return.Select("图纸序号='ZP'");
                                            drs_RTZP[0]["图纸编号"] = firstNumber1;
                                            drs_RTZP[0]["图纸编号"] = str_图纸类型1 + firstNumber1.ToString().PadLeft(i_编码位数, '0');
                                        }

                                    }
                                    if (dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() != "ZP")
                                    {

                                        //不再处理ZP了
                                        //有无空号？
                                        sql = "select * from 图纸编号表 where 作废 = 1";
                                        DataTable dt_空号 = commandManager.ExecuteNonQuery(conn,trans, sql, "图纸编号表");

                                        DataRow[] drs_空号 = dt_空号.Select("图纸类型='" + str_图纸类型 + "' and 作废=" + 1, "ID ASC");
                                        //判断是否有空号？
                                        if (drs_空号.Length > 0)
                                        {
                                            //有空号
                                            DataRow dr_首空 = drs_空号[0];
                                            //不再处理ZP了
                                            sql = "update 图纸编号表 set 机床型号 = '" + str_机床型号
                                                + "',A号= '" + str_A号
                                                + "',零件序号= '" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString()
                                                + "',申请人= '" + str_申请人
                                                + "',审核人= '" + str_审核人
                                                + "',申请日期= '" + str_申请日期
                                                + "',作废= " + 0
                                                + " where ID=" + int.Parse(dr_首空["ID"].ToString());
                                            commandManager.update(conn,trans, sql);
                                            DataRow[] drs_RTZP = dt_return.Select("图纸序号= '" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "'");
                                            drs_RTZP[0]["图纸编号"] = dr_首空["图纸编号"];
                                            drs_RTZP[0]["图纸编号"] = str_图纸类型 + int.Parse(dr_首空["图纸编号"].ToString()).ToString().PadLeft(i_编码位数, '0');
                                            //MessageBox.Show(dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString());
                                        }
                                        else
                                        {
                                            //无空号
                                            //不再处理ZP了
                                            firstNumber++;
                                            sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                            "values('" + str_机床型号 + "','" + str_A号 + "','" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "','"
                                            + firstNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                            + 0 + ",'" + str_图纸类型 + "')";
                                            commandManager.update(conn,trans, sql);
                                            dt_return.Rows[ir]["图纸编号"] = firstNumber;
                                            dt_return.Rows[ir]["图纸编号"] = str_图纸类型 + firstNumber.ToString().PadLeft(i_编码位数, '0');
                                        }
                                    }
                                }
                                else
                                {
                                    //ir>0
                                    //不再处理ZP
                                    if (dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() != "ZP")
                                    {
                                        //不再处理ZP了
                                        //有无空号？

                                        sql = "select * from 图纸编号表 where 作废 = 1";
                                        DataTable dt_空号 = commandManager.ExecuteNonQuery(conn,trans, sql, "图纸编号表");

                                        DataRow[] drs_空号 = dt_空号.Select("图纸类型='" + str_图纸类型 + "' and 作废=" + 1, "ID ASC");
                                        //判断是否有空号？
                                        if (drs_空号.Length > 0)
                                        {
                                            //有空号
                                            DataRow dr_首空 = drs_空号[0];
                                            //不再处理ZP了
                                            sql = "update 图纸编号表 set 机床型号 = '" + str_机床型号
                                                + "',A号= '" + str_A号
                                                + "',零件序号= '" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString()
                                                + "',申请人= '" + str_申请人
                                                + "',审核人= '" + str_审核人
                                                + "',申请日期= '" + str_申请日期
                                                + "',作废= " + 0
                                                + " where ID=" + int.Parse(dr_首空["ID"].ToString());
                                            commandManager.update(conn,trans, sql);
                                            DataRow[] drs_RTZP = dt_return.Select("图纸序号= '" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "'");
                                            drs_RTZP[0]["图纸编号"] = dr_首空["图纸编号"];
                                            drs_RTZP[0]["图纸编号"] = str_图纸类型 + int.Parse(dr_首空["图纸编号"].ToString()).ToString().PadLeft(i_编码位数, '0');
                                        }
                                        else
                                        {
                                            //无空号
                                            //不再处理ZP了
                                            firstNumber++;
                                            sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                            "values('" + str_机床型号 + "','" + str_A号 + "','" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "','"
                                            + firstNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                            + 0 + ",'" + str_图纸类型 + "')";
                                            commandManager.update(conn,trans, sql);
                                            dt_return.Rows[ir]["图纸编号"] = firstNumber;
                                            dt_return.Rows[ir]["图纸编号"] = str_图纸类型 + firstNumber.ToString().PadLeft(i_编码位数, '0');
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                #endregion
                //trans.Commit();
                return dt_return;
            }
            catch (System.Exception ex)
            {
                //Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(ex.Message);
                //if (trans!=null)
                //{
                //    trans.Rollback();
                //}
                return null;
            }
            finally
            {
                //if (conn != null)
                //{
                //    conn.Close();
                //}
            }
        }
        /// <summary>
        /// conn，trans是传入的，不需要进行处理。
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="dt_文件夹内文件信息"></param>
        /// <param name="str_机床型号"></param>
        /// <param name="str_A号"></param>
        /// <param name="str_申请人"></param>
        /// <param name="str_审核人"></param>
        /// <param name="str_申请日期"></param>
        /// <param name="i_编码位数"></param>
        /// <returns></returns>
        public static DataTable DoNumberForApply4CNumber(
            myDBConnection conn,
            myDBTransaction trans,
            DataTable dt_文件夹内文件信息, 
            string str_机床型号, 
            string str_A号, 
            string str_申请人, 
            string str_审核人, 
            string str_申请日期, 
            int i_编码位数)
        {
            //Editor ed = acDocMgr.MdiActiveDocument.Editor;
            DataTable dt_return;
            dt_return = dt_文件夹内文件信息.Copy();
            dt_return.Columns.Add("图纸编号");

            DataView dataView = dt_文件夹内文件信息.DefaultView;
            DataTable dt_distinctForType = dataView.ToTable(true, "图纸类型");//J，D，L，Z，4C，？？？
            DataTable dt_distinctForSize = dataView.ToTable(true, "图纸大小");//0，1，2，3，4

            string sql = string.Empty;
            try
            {
                sql = "select * from 图纸编号表 where 1=1";
                DataTable dt_图纸编号表 = commandManager.ExecuteNonQuery(conn, trans, sql, "图纸编号表");
                sql = "select * from 初始编码 where 1=1";
                DataTable dt_初始编码 = commandManager.ExecuteNonQuery(conn, trans, sql, "初始编码");

                //判断图纸编号表是否为空？
                #region 图纸编号表为空
                if (dt_图纸编号表.Rows.Count == 0)
                {
                    //图纸编号表为空
                    foreach (DataRow dr_图纸大小 in dt_distinctForSize.Rows)
                    {
                        int firstNumber = 0;
                        string str_图纸类型 = dr_图纸大小["图纸大小"].ToString() + dt_distinctForType.Rows[0]["图纸类型"].ToString();
                        firstNumber = int.Parse(dt_初始编码.Rows[0][str_图纸类型].ToString());
                        for (int ir = 0; ir < dt_文件夹内文件信息.Rows.Count; ir++)
                        {
                            if (dt_文件夹内文件信息.Rows[ir]["图纸大小"].ToString() == dr_图纸大小["图纸大小"].ToString())
                            {


                                if (ir == 0)
                                {
                                    //先处理4C
                                    int first4CNumber = int.Parse(dt_初始编码.Rows[0]["4C"].ToString());
                                    first4CNumber++;
                                    sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                    "values('" + str_机床型号 + "','" + str_A号 + "','" + "ZP-4C" + "','"
                                    + first4CNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                    + 0 + ",'" + "4C" + "')";
                                    commandManager.update(conn, trans, sql);
                                    DataRow drs_4C = dt_return.NewRow();
                                    drs_4C["图纸编号"] = "4C" + first4CNumber.ToString().PadLeft(i_编码位数, '0');
                                    drs_4C["图纸序号"] = "ZP-4C";
                                    dt_return.Rows.Add(drs_4C);
                                }

                                //不再处理ZP
                                if (dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() != "ZP")
                                {
                                    //MessageBox.Show("不再处理ZP了");
                                    //不再处理ZP了
                                    firstNumber++;
                                    sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                    "values('" + str_机床型号 + "','" + str_A号 + "','" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "','"
                                    + firstNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                    + 0 + ",'" + str_图纸类型 + "')";
                                    commandManager.update(conn, trans, sql);
                                    dt_return.Rows[ir]["图纸编号"] = firstNumber;
                                    dt_return.Rows[ir]["图纸编号"] = str_图纸类型 + firstNumber.ToString().PadLeft(i_编码位数, '0');
                                }
                            }
                        }
                    }
                }
                #endregion

                #region 图纸编号表不为空
                else
                {
                    //图纸编号表不为空
                    foreach (DataRow dr_图纸大小 in dt_distinctForSize.Rows)
                    {
                        int firstNumber = 0;
                        string str_图纸类型 = dr_图纸大小["图纸大小"].ToString() + dt_distinctForType.Rows[0]["图纸类型"].ToString();
                        DataRow[] drs_图纸编号表 = dt_图纸编号表.Select("图纸类型='" + str_图纸类型 + "'", "ID ASC");
                        if (drs_图纸编号表.Length == 0)
                        {
                            firstNumber = int.Parse(dt_初始编码.Rows[0][str_图纸类型].ToString());
                        }
                        else
                        {
                            firstNumber = int.Parse(drs_图纸编号表[drs_图纸编号表.Length - 1]["图纸编号"].ToString());
                        }

                        for (int ir = 0; ir < dt_文件夹内文件信息.Rows.Count; ir++)
                        {
                            if (dt_文件夹内文件信息.Rows[ir]["图纸大小"].ToString() == dr_图纸大小["图纸大小"].ToString())
                            {

                                if (ir == 0)
                                {
                                    //先处理4C
                                    //有无空号？
                                    sql = "select * from 图纸编号表 where 作废 = 1";
                                    DataTable dt_空号 = commandManager.ExecuteNonQuery(conn, trans, sql, "图纸编号表");

                                    DataRow[] drs_空号 = dt_空号.Select("图纸类型='" + "4C" + "' and 作废=" + 1, "ID ASC");
                                    //判断是否有空号？
                                    if (drs_空号.Length > 0)
                                    {
                                        DataRow dr_首空 = drs_空号[0];
                                        sql = "update 图纸编号表 set 机床型号 = '" + str_机床型号
                                            + "',A号= '" + str_A号
                                            + "',零件序号= '" + "ZP-4C"
                                            + "',申请人= '" + str_申请人
                                            + "',审核人= '" + str_审核人
                                            + "',申请日期= '" + str_申请日期
                                            + "',作废= " + 0
                                            + " where ID=" + int.Parse(dr_首空["ID"].ToString());
                                        commandManager.update(conn, trans, sql);

                                        DataRow drs_4C = dt_return.NewRow();
                                        drs_4C["图纸编号"] = "4C" + int.Parse(dr_首空["图纸编号"].ToString()).ToString().PadLeft(i_编码位数, '0');
                                        drs_4C["图纸序号"] = "ZP-4C";
                                        dt_return.Rows.Add(drs_4C);
                                    }
                                    else
                                    {
                                        //先处理4C
                                        int first4CNumber = 0;
                                        DataRow[] drs_图纸编号表4C = dt_图纸编号表.Select("图纸类型='" + "4C" + "'", "ID ASC");
                                        if (drs_图纸编号表4C.Length == 0)
                                        {
                                            first4CNumber = int.Parse(dt_初始编码.Rows[0]["4C"].ToString());
                                        }
                                        else
                                        {
                                            first4CNumber = int.Parse(drs_图纸编号表4C[drs_图纸编号表4C.Length - 1]["图纸编号"].ToString());
                                        }

                                        first4CNumber++;
                                        sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                        "values('" + str_机床型号 + "','" + str_A号 + "','" + "ZP-4C" + "','"
                                        + first4CNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                        + 0 + ",'" + "4C" + "')";
                                        commandManager.update(conn, trans, sql);
                                        DataRow drs_4C = dt_return.NewRow();
                                        drs_4C["图纸编号"] = "4C" + first4CNumber.ToString().PadLeft(i_编码位数, '0');
                                        drs_4C["图纸序号"] = "ZP-4C";
                                        dt_return.Rows.Add(drs_4C);
                                    }

                                }

                                //ir>0
                                //不再处理ZP
                                if (dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() != "ZP")
                                {
                                    //不再处理ZP了
                                    //有无空号？

                                    sql = "select * from 图纸编号表 where 作废 = 1";
                                    DataTable dt_空号 = commandManager.ExecuteNonQuery(conn, trans, sql, "图纸编号表");

                                    DataRow[] drs_空号 = dt_空号.Select("图纸类型='" + str_图纸类型 + "' and 作废=" + 1, "ID ASC");
                                    //判断是否有空号？
                                    if (drs_空号.Length > 0)
                                    {
                                        //有空号
                                        DataRow dr_首空 = drs_空号[0];
                                        //不再处理ZP了
                                        sql = "update 图纸编号表 set 机床型号 = '" + str_机床型号
                                            + "',A号= '" + str_A号
                                            + "',零件序号= '" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString()
                                            + "',申请人= '" + str_申请人
                                            + "',审核人= '" + str_审核人
                                            + "',申请日期= '" + str_申请日期
                                            + "',作废= " + 0
                                            + " where ID=" + int.Parse(dr_首空["ID"].ToString());
                                        commandManager.update(conn, trans, sql);
                                        DataRow[] drs_RTZP = dt_return.Select("图纸序号= '" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "'");
                                        drs_RTZP[0]["图纸编号"] = dr_首空["图纸编号"];
                                        drs_RTZP[0]["图纸编号"] = str_图纸类型 + int.Parse(dr_首空["图纸编号"].ToString()).ToString().PadLeft(i_编码位数, '0');
                                    }
                                    else
                                    {
                                        //无空号
                                        //不再处理ZP了
                                        firstNumber++;
                                        sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                        "values('" + str_机床型号 + "','" + str_A号 + "','" + dt_文件夹内文件信息.Rows[ir]["图纸序号"].ToString() + "','"
                                        + firstNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                                        + 0 + ",'" + str_图纸类型 + "')";
                                        commandManager.update(conn, trans, sql);
                                        dt_return.Rows[ir]["图纸编号"] = firstNumber;
                                        dt_return.Rows[ir]["图纸编号"] = str_图纸类型 + firstNumber.ToString().PadLeft(i_编码位数, '0');
                                    }
                                }

                            }
                        }
                    }
                }
                #endregion
                return dt_return;
 
            }
            catch (System.Exception ex)
            {
                //ed.WriteMessage("\nError during copy: " + ex.Message);
                // Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(ex.Message);
                //if (trans != null)
                //{
                //    trans.Rollback();
                //}
                return null;
            }
            finally
            {
                //if (conn != null)
                //{
                //    conn.Close();
                //}
            }
        }
        /// <summary>
        /// conn，trans是传入的，不需要进行处理。
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="dt_文件夹内文件信息"></param>
        /// <param name="str_机床型号"></param>
        /// <param name="str_A号"></param>
        /// <param name="str_申请人"></param>
        /// <param name="str_审核人"></param>
        /// <param name="str_申请日期"></param>
        /// <param name="i_编码位数"></param>
        /// <returns></returns>
        public static DataTable DoNumberForApply4CNumberNoDwg(
            myDBConnection conn, 
            myDBTransaction trans, 
            DataTable dt_文件夹内文件信息, 
            string str_机床型号, 
            string str_A号,
            string str_申请人, 
            string str_审核人,
            string str_申请日期, 
            int i_编码位数)
        {
            DataTable dt_return;
            dt_return = dt_文件夹内文件信息.Copy();
            dt_return.Columns.Add("图纸编号");

            DataView dataView = dt_文件夹内文件信息.DefaultView;
            DataTable dt_distinctForType = dataView.ToTable(true, "图纸类型");//J，D，L，Z，4C，？？？
            DataTable dt_distinctForSize = dataView.ToTable(true, "图纸大小");//0，1，2，3，4

            string sql = string.Empty;
            try
            {
                conn = connectionManager.getConnection();
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                trans = conn.BeginTransaction();

                sql = "select * from 图纸编号表 where 1=1";
                DataTable dt_图纸编号表 = commandManager.ExecuteNonQuery(conn,trans, sql, "图纸编号表");
                sql = "select * from 初始编码 where 1=1";
                DataTable dt_初始编码 = commandManager.ExecuteNonQuery(conn,trans, sql, "初始编码");

                //判断图纸编号表是否为空？
                #region 图纸编号表为空
                if (dt_图纸编号表.Rows.Count == 0)
                {
                    //先处理4C
                    int first4CNumber = int.Parse(dt_初始编码.Rows[0]["4C"].ToString());
                    first4CNumber++;
                    sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                    "values('" + str_机床型号 + "','" + str_A号 + "','" + "ZP-4C" + "','"
                    + first4CNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                    + 0 + ",'" + "4C" + "')";
                    commandManager.update(conn,trans, sql);
                    DataRow drs_4C = dt_return.NewRow();
                    drs_4C["图纸编号"] = "4C" + first4CNumber.ToString().PadLeft(i_编码位数, '0');
                    drs_4C["图纸序号"] = "ZP-4C";
                    dt_return.Rows.Add(drs_4C);
                }
                #endregion
                #region 图纸编号表不为空
                else
                {
                    //图纸编号表不为空
                    //先处理4C
                    //有无空号？
                    sql = "select * from 图纸编号表 where 作废 = 1";
                    DataTable dt_空号 = commandManager.ExecuteNonQuery(conn,trans, sql, "图纸编号表");

                    DataRow[] drs_空号 = dt_空号.Select("图纸类型='" + "4C" + "' and 作废=" + 1, "ID ASC");
                    //判断是否有空号？
                    if (drs_空号.Length > 0)
                    {
                        DataRow dr_首空 = drs_空号[0];
                        sql = "update 图纸编号表 set 机床型号 = '" + str_机床型号
                            + "',A号= '" + str_A号
                            + "',零件序号= '" + "ZP-4C"
                            + "',申请人= '" + str_申请人
                            + "',审核人= '" + str_审核人
                            + "',申请日期= '" + str_申请日期
                            + "',作废= " + 0
                            + " where ID=" + int.Parse(dr_首空["ID"].ToString());
                        commandManager.update(conn,trans, sql);

                        DataRow drs_4C = dt_return.NewRow();
                        drs_4C["图纸编号"] = "4C" + int.Parse(dr_首空["图纸编号"].ToString()).ToString().PadLeft(i_编码位数, '0');
                        drs_4C["图纸序号"] = "ZP-4C";
                        dt_return.Rows.Add(drs_4C);
                    }
                    else
                    {
                        //先处理4C
                        int first4CNumber = 0;
                        DataRow[] drs_图纸编号表4C = dt_图纸编号表.Select("图纸类型='" + "4C" + "'", "ID ASC");
                        if (drs_图纸编号表4C.Length == 0)
                        {
                            first4CNumber = int.Parse(dt_初始编码.Rows[0]["4C"].ToString());
                        }
                        else
                        {
                            first4CNumber = int.Parse(drs_图纸编号表4C[drs_图纸编号表4C.Length - 1]["图纸编号"].ToString());
                        }

                        first4CNumber++;
                        sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                        "values('" + str_机床型号 + "','" + str_A号 + "','" + "ZP-4C" + "','"
                        + first4CNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                        + 0 + ",'" + "4C" + "')";
                        commandManager.update(conn,trans, sql);
                        DataRow drs_4C = dt_return.NewRow();
                        drs_4C["图纸编号"] = "4C" + first4CNumber.ToString().PadLeft(i_编码位数, '0');
                        drs_4C["图纸序号"] = "ZP-4C";
                        dt_return.Rows.Add(drs_4C);
                    }
                }
                #endregion
                //trans.Commit();
                return dt_return;
            }
            catch (System.Exception ex)
            {
               // Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(ex.Message);
                //if (trans != null)
                //{
                //    trans.Rollback();
                //}
                return null;
            }
            finally
            {
                //if (conn != null)
                //{
                //    conn.Close();
                //}
            }

            
        }
        /// <summary>
        /// conn，trans是传入的，不需要进行处理。
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="dt_文件夹内文件信息"></param>
        /// <param name="str_机床型号"></param>
        /// <param name="str_A号"></param>
        /// <param name="str_申请人"></param>
        /// <param name="str_审核人"></param>
        /// <param name="str_申请日期"></param>
        /// <param name="i_编码位数"></param>
        /// <returns></returns>
        public static DataTable DoNumberForApply4CNumberNoDwgBiaoZhunJian(
            myDBConnection conn, 
            myDBTransaction trans, 
            DataTable dt_文件夹内文件信息,
            string str_机床型号, string str_A号,
            string str_申请人,
            string str_审核人, 
            string str_申请日期,
            int i_编码位数)
        {
            DataTable dt_return;
            dt_return = dt_文件夹内文件信息.Copy();
            dt_return.Columns.Add("图纸编号");

            DataView dataView = dt_文件夹内文件信息.DefaultView;
            DataTable dt_distinctForType = dataView.ToTable(true, "图纸类型");//J，D，L，Z，4C，？？？
            DataTable dt_distinctForSize = dataView.ToTable(true, "图纸大小");//0，1，2，3，4

            string sql = string.Empty;
            //myDBConnection conn = null;
            //myDBTransaction trans = null;
            try
            {
                conn = connectionManager.getConnection();
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                trans = conn.BeginTransaction();

                sql = "select * from 图纸编号表 where 1=1";
                DataTable dt_图纸编号表 = commandManager.ExecuteNonQuery(conn, trans, sql, "图纸编号表");
                sql = "select * from 初始编码 where 1=1";
                DataTable dt_初始编码 = commandManager.ExecuteNonQuery(conn, trans, sql, "初始编码");

                //判断图纸编号表是否为空？
                #region 图纸编号表为空
                if (dt_图纸编号表.Rows.Count == 0)
                {
                    //先处理4C
                    int first4CNumber = int.Parse(dt_初始编码.Rows[0]["4C"].ToString());
                    first4CNumber++;
                    sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                    "values('" + str_机床型号 + "','" + str_A号 + "','" + "4C-标准件" + "','"
                    + first4CNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                    + 0 + ",'" + "4C" + "')";
                    commandManager.update(conn, trans, sql);
                    DataRow drs_4C = dt_return.NewRow();
                    drs_4C["图纸编号"] = "4C" + first4CNumber.ToString().PadLeft(i_编码位数, '0');
                    drs_4C["图纸序号"] = "4C-标准件";
                    dt_return.Rows.Add(drs_4C);
                }
                #endregion

                #region 图纸编号表不为空
                else
                {
                    //图纸编号表不为空
                    //先处理4C
                    //有无空号？
                    sql = "select * from 图纸编号表 where 作废 = 1";
                    DataTable dt_空号 = commandManager.ExecuteNonQuery(conn, trans, sql, "图纸编号表");

                    DataRow[] drs_空号 = dt_空号.Select("图纸类型='" + "4C" + "' and 作废=" + 1, "ID ASC");
                    //判断是否有空号？
                    if (drs_空号.Length > 0)
                    {
                        DataRow dr_首空 = drs_空号[0];
                        sql = "update 图纸编号表 set 机床型号 = '" + str_机床型号
                            + "',A号= '" + str_A号
                            + "',零件序号= '" + "4C-标准件"
                            + "',申请人= '" + str_申请人
                            + "',审核人= '" + str_审核人
                            + "',申请日期= '" + str_申请日期
                            + "',作废= " + 0
                            + " where ID=" + int.Parse(dr_首空["ID"].ToString());
                        commandManager.update(conn, trans, sql);

                        DataRow drs_4C = dt_return.NewRow();
                        drs_4C["图纸编号"] = "4C" + int.Parse(dr_首空["图纸编号"].ToString()).ToString().PadLeft(i_编码位数, '0');
                        drs_4C["图纸序号"] = "4C-标准件";
                        dt_return.Rows.Add(drs_4C);
                    }
                    else
                    {
                        //先处理4C
                        int first4CNumber = 0;
                        DataRow[] drs_图纸编号表4C = dt_图纸编号表.Select("图纸类型='" + "4C" + "'", "ID ASC");
                        if (drs_图纸编号表4C.Length == 0)
                        {
                            first4CNumber = int.Parse(dt_初始编码.Rows[0]["4C"].ToString());
                        }
                        else
                        {
                            first4CNumber = int.Parse(drs_图纸编号表4C[drs_图纸编号表4C.Length - 1]["图纸编号"].ToString());
                        }

                        first4CNumber++;
                        sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                        "values('" + str_机床型号 + "','" + str_A号 + "','" + "4C-标准件" + "','"
                        + first4CNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 0 + ",'" + "" + "',"
                        + 0 + ",'" + "4C" + "')";
                        commandManager.update(conn, trans, sql);
                        DataRow drs_4C = dt_return.NewRow();
                        drs_4C["图纸编号"] = "4C" + first4CNumber.ToString().PadLeft(i_编码位数, '0');
                        drs_4C["图纸序号"] = "4C-标准件";
                        dt_return.Rows.Add(drs_4C);
                    }
                }
                #endregion
                trans.Commit();
                return dt_return;
            }
            catch (System.Exception ex)
            {
                //Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(ex.Message);
                //if (trans != null)
                //{
                //    trans.Rollback();
                //}
                return null;
            }
            finally
            {
                //if (conn != null)
                //{
                //    conn.Close();
                //}
            }


        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt_数量"></param>
        /// <param name="str_外协单位"></param>
        /// <param name="str_申请人"></param>
        /// <param name="str_审核人"></param>
        /// <param name="str_申请日期"></param>
        /// <param name="i_编码位数"></param>
        /// <returns></returns>
        public static DataTable DoNumberForApplyOut(
            DataTable dt_数量,
            string str_外协单位,
            string str_申请人, 
            string str_审核人,
            string str_申请日期,
            int i_编码位数)
        {
            //Editor ed = acDocMgr.MdiActiveDocument.Editor;
            DataTable dt_return=new DataTable();
            dt_return.Columns.Add("图纸编号");
            dt_return.Columns.Add("图纸类型");
            dt_return.Columns.Add("申请人");
            dt_return.Columns.Add("申请日期");
            dt_return.Columns.Add("外协单位");
            string sql = string.Empty;
            myDBConnection cnn = null;
            myDBTransaction trans = null;
            try
            {
                cnn = connectionManager.getConnection();
                if (cnn.State != ConnectionState.Open)
                {
                    cnn.Open();
                }
                trans = cnn.BeginTransaction();


                sql = "select * from 图纸编号表 where 1=1";
                DataTable dt_图纸编号表 = commandManager.ExecuteNonQuery(cnn, trans, sql, "图纸编号表");
                sql = "select * from 初始编码 where 1=1";
                DataTable dt_初始编码 = commandManager.ExecuteNonQuery(cnn, trans, sql, "初始编码");


                //判断图纸编号表是否为空？
                #region 图纸编号表为空
                if (dt_图纸编号表.Rows.Count == 0)
                {
                    //图纸编号表为空
                    foreach (DataColumn dc_图纸大小 in dt_数量.Columns)
                    {
                        int firstNumber = 0;
                        string str_图纸类型 = dc_图纸大小.ColumnName;
                        int i_nums = int.Parse(dt_数量.Rows[0][dc_图纸大小.ColumnName].ToString());
                        firstNumber = int.Parse(dt_初始编码.Rows[0][str_图纸类型].ToString());
                        for (int ir = 0; ir < i_nums; ir++)
                        {
                            firstNumber++;
                            sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                            "values('" + "" + "','" + "" + "','" + "" + "','"
                            + firstNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 1 + ",'" + str_外协单位 + "',"
                            + 0 + ",'" + str_图纸类型 + "')";
                            commandManager.update(cnn, trans, sql);

                            DataRow drs_rt = dt_return.NewRow();
                            drs_rt["图纸编号"] = str_图纸类型 + firstNumber.ToString().PadLeft(i_编码位数, '0');
                            drs_rt["图纸类型"] = str_图纸类型;
                            drs_rt["申请人"] = str_申请人;
                            drs_rt["申请日期"] = str_申请日期;
                            drs_rt["外协单位"] = str_外协单位;
                            dt_return.Rows.Add(drs_rt);
                        }
                    }
                }
                #endregion

                #region 图纸编号表不为空
                else
                {
                    foreach (DataColumn dc_图纸大小 in dt_数量.Columns)
                    {
                        int firstNumber = 0;
                        string str_图纸类型 = dc_图纸大小.ColumnName;
                        int i_nums = int.Parse(dt_数量.Rows[0][dc_图纸大小.ColumnName].ToString());
                        DataRow[] drs_图纸编号表 = dt_图纸编号表.Select("图纸类型='" + str_图纸类型 + "'", "ID ASC");
                        if (drs_图纸编号表.Length == 0)
                        {
                            firstNumber = int.Parse(dt_初始编码.Rows[0][str_图纸类型].ToString());
                        }
                        else
                        {
                            firstNumber = int.Parse(drs_图纸编号表[drs_图纸编号表.Length - 1]["图纸编号"].ToString());
                        }
                        for (int ir = 0; ir < i_nums; ir++)
                        {
                            sql = "select * from 图纸编号表 where 作废 = 1";
                            DataTable dt_空号 = commandManager.ExecuteNonQuery(cnn, trans, sql, "图纸编号表");

                            DataRow[] drs_空号 = dt_空号.Select("图纸类型='" + str_图纸类型 + "' and 作废=" + 1, "ID ASC");
                            //判断是否有空号？
                            if (drs_空号.Length > 0)
                            {
                                //有空号
                                DataRow dr_首空 = drs_空号[0];
                                //不再处理ZP了
                                sql = "update 图纸编号表 set 机床型号 = '" + ""
                                    + "',A号= '" + ""
                                    + "',零件序号= '" + ""
                                    + "',申请人= '" + str_申请人
                                    + "',审核人= '" + str_审核人
                                    + "',申请日期= '" + str_申请日期
                                    + "',外协单位= '" + str_外协单位
                                    + "',外协= " + 1
                                    + " ,作废= " + 0
                                    + " where ID=" + int.Parse(dr_首空["ID"].ToString());
                                commandManager.update(cnn, trans, sql);
                                DataRow drs_rt = dt_return.NewRow();
                                drs_rt["图纸编号"] = str_图纸类型 + int.Parse(dr_首空["图纸编号"].ToString()).ToString().PadLeft(i_编码位数, '0');
                                drs_rt["图纸类型"] = str_图纸类型;
                                drs_rt["申请人"] = str_申请人;
                                drs_rt["申请日期"] = str_申请日期;
                                drs_rt["外协单位"] = str_外协单位;
                                dt_return.Rows.Add(drs_rt);
                            }
                            else
                            {
                                //无空号
                                firstNumber++;
                                sql = "insert into 图纸编号表(机床型号,A号,零件序号,图纸编号,名称,规格,申请人,审核人,申请日期,外协,外协单位,作废,图纸类型)" +
                                "values('" + "" + "','" + "" + "','" + "" + "','"
                                + firstNumber.ToString() + "','" + "" + "','" + "" + "','" + str_申请人 + "','" + str_审核人 + "','" + str_申请日期 + "'," + 1 + ",'" + str_外协单位 + "',"
                                + 0 + ",'" + str_图纸类型 + "')";
                                commandManager.update(cnn, trans, sql);

                                DataRow drs_rt = dt_return.NewRow();
                                drs_rt["图纸编号"] = str_图纸类型 + firstNumber.ToString().PadLeft(i_编码位数, '0');
                                drs_rt["图纸类型"] = str_图纸类型;
                                drs_rt["申请人"] = str_申请人;
                                drs_rt["申请日期"] = str_申请日期;
                                drs_rt["外协单位"] = str_外协单位;
                                dt_return.Rows.Add(drs_rt);
                            }
                        }
                    }
                }
                #endregion
                trans.Commit();
                return dt_return;
                
            }
            catch (System.Exception ex)
            {
                //ed.WriteMessage("\nError during copy: " + ex.Message);
                // Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(ex.Message);
                if (trans != null)
                {
                    trans.Rollback();
                }
                return null;
            }
            finally
            {
                if (cnn != null)
                {
                    cnn.Close();
                }
            }
        }
        /// <summary>
        /// 重新命名，内部没有的操作数据库。
        /// </summary>
        /// <param name="str_路径"></param>
        /// <param name="dt_文件夹内文件信息"></param>
        /// <returns></returns>
        public static bool DoRename(string str_路径, DataTable dt_文件夹内文件信息)
        {
            bool re = false;
            try
            {
                Computer MyComputer = new Computer();
                foreach (DataRow dr in dt_文件夹内文件信息.Rows)
                {
                    if (dr["图纸序号"].ToString() != "ZP-4C")
                    {
                        string str_old = str_路径 + "\\" + dr["原文件名"].ToString() + dr["扩展名"].ToString();
                        string str_new = dr["图纸编号"].ToString() +"-"+ dr["图纸序号"].ToString() + dr["扩展名"].ToString();
                        MyComputer.FileSystem.RenameFile(str_old, str_new);
                    }
                }
                re = true;
            }
            catch (System.Exception ex)
            {
                Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(ex.Message);
                return re;
            }
            return re;
        }
        /// <summary>
        /// 没有操作数据库。
        /// </summary>
        /// <param name="str_路径"></param>
        /// <param name="dt_文件夹内文件信息"></param>
        /// <param name="s_用户名"></param>
        /// <param name="s_审核人"></param>
        /// <param name="s_日期"></param>
        /// <param name="s_机床型号"></param>
        public static void DoTitle(
            string str_路径,
            DataTable dt_文件夹内文件信息, 
            string s_用户名, 
            string s_审核人,
            string s_日期, 
            string s_机床型号)
        {
            DocumentCollection acDocMgr = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager;
            Editor ed = acDocMgr.MdiActiveDocument.Editor;
            Autodesk.AutoCAD.DatabaseServices.Database destDb = acDocMgr.MdiActiveDocument.Database;
            string sql;
            using (acDocMgr.MdiActiveDocument.LockDocument())
            {
                myDBConnection cnn = null;
                myDBTransaction trans = null;
                try
                {
                    cnn = connectionManager.getConnection();
                    if (cnn.State != ConnectionState.Open)
                    {
                        cnn.Open();
                    }
                    trans = cnn.BeginTransaction();
                    foreach (DataRow dr in dt_文件夹内文件信息.Rows)
                    {
                        if (dr["图纸序号"].ToString() != "ZP-4C")
                        {
                            Autodesk.AutoCAD.DatabaseServices.Database sourceDb = new Autodesk.AutoCAD.DatabaseServices.Database(false, false);
                            string str_file = str_路径 + "\\" + dr["图纸编号"].ToString() + "-" + dr["图纸序号"].ToString() + dr["扩展名"].ToString();
                            string str_图纸大小 = "A" + dr["图纸大小"].ToString();
                            sourceDb.ReadDwgFile(str_file, System.IO.FileShare.ReadWrite, true, "");
                            Autodesk.AutoCAD.DatabaseServices.TransactionManager tm = sourceDb.TransactionManager;
                            using (Autodesk.AutoCAD.DatabaseServices.Transaction trs_Transaction = tm.StartTransaction())
                            {
                                Autodesk.AutoCAD.DatabaseServices.BlockTable bt = (Autodesk.AutoCAD.DatabaseServices.BlockTable)tm.GetObject(sourceDb.BlockTableId, Autodesk.AutoCAD.DatabaseServices.OpenMode.ForWrite, false);
                                foreach (Autodesk.AutoCAD.DatabaseServices.ObjectId btrId in bt)
                                {
                                    Autodesk.AutoCAD.DatabaseServices.BlockTableRecord btr = (Autodesk.AutoCAD.DatabaseServices.BlockTableRecord)tm.GetObject(btrId, Autodesk.AutoCAD.DatabaseServices.OpenMode.ForWrite, false);

                                    if (btr.Name == str_图纸大小||btr.Name==(str_图纸大小+@"图框"))//图块名称，这次发生变化了2020-5-25
                                    {
                                        //MessageBox.Show(btr.Name);
                                        Autodesk.AutoCAD.DatabaseServices.ObjectIdCollection btrObjIds = new Autodesk.AutoCAD.DatabaseServices.ObjectIdCollection();
                                        btrObjIds = btr.GetBlockReferenceIds(true, true);
                                        foreach (Autodesk.AutoCAD.DatabaseServices.ObjectId objId in btrObjIds)
                                        {
                                            Autodesk.AutoCAD.DatabaseServices.BlockReference blkRef = (Autodesk.AutoCAD.DatabaseServices.BlockReference)trs_Transaction.GetObject(objId, Autodesk.AutoCAD.DatabaseServices.OpenMode.ForWrite);
                                            List<BlockAttributeEntity> lst_块属性对集合 = new List<BlockAttributeEntity>();
                                            lst_块属性对集合.Add(new BlockAttributeEntity("零部件图号", dr["图纸编号"].ToString()));
                                            lst_块属性对集合.Add(new BlockAttributeEntity("零部件图号_1", dr["图纸编号"].ToString()));
                                            lst_块属性对集合.Add(new BlockAttributeEntity("机床型号", s_机床型号));
                                            lst_块属性对集合.Add(new BlockAttributeEntity("设计者", s_用户名));
                                            lst_块属性对集合.Add(new BlockAttributeEntity("设计日期", s_日期));
                                            lst_块属性对集合.Add(new BlockAttributeEntity("审核者", s_审核人));
                                            lst_块属性对集合.Add(new BlockAttributeEntity("审核日期", s_日期));
                                            TitleAttributeSetDal cls_ZJSetAttributeValue= new TitleAttributeSetDal(sourceDb, trs_Transaction, blkRef, lst_块属性对集合);
                                            cls_ZJSetAttributeValue.Do();                         
                                            TitleAttributeGetDal cls_ZJGetAttributeValue
                                                        = new TitleAttributeGetDal(sourceDb, trs_Transaction, blkRef);
                                            cls_ZJGetAttributeValue.Do();
                                            lst_块属性对集合 = new List<BlockAttributeEntity>();
                                            lst_块属性对集合 = cls_ZJGetAttributeValue.LstAttr;
                                            foreach (BlockAttributeEntity an in lst_块属性对集合)
                                            {
                                                string s_图纸序号 = dr["图纸编号"].ToString().Substring(2, dr["图纸编号"].ToString().Length - 2);
                                                if (an.Tag=="零部件名称")
                                                {
                                                    sql = "update 图纸编号表 set 名称 = '" + an.TextString + "'"
                                                         + " where 图纸类型 = '" + dr["图纸大小"].ToString() + dr["图纸类型"].ToString() + "' and "
                                                         + "图纸编号 =  " + int.Parse(s_图纸序号);
                                                    commandManager.update(cnn,trans, sql);
                                                }
                                                if (an.Tag == "材料")
                                                {
                                                    sql = "update 图纸编号表 set 规格 = '" + an.TextString + "'"
                                                        + " where 图纸类型 = '" + dr["图纸大小"].ToString() + dr["图纸类型"].ToString() + "' and "
                                                        + "图纸编号 =  " + int.Parse(s_图纸序号);
                                                    commandManager.update(cnn,trans, sql);
                                                }
                                            }
                                        }
                                    }
                                    //btr.Dispose();
                                }
                                trs_Transaction.Commit();
                                trs_Transaction.Dispose();
                            }
                            sourceDb.SaveAs(str_file, Autodesk.AutoCAD.DatabaseServices.DwgVersion.AC1015);
                        }
                    }
                    trans.Commit();
                }
                catch (Autodesk.AutoCAD.Runtime.Exception ex)
                {
                    ed.WriteMessage("\nError during copy: " + ex.Message);
                   // Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(ex.Message);
                    if (trans != null)
                    {
                        trans.Rollback();
                    }
                }
                finally
                {
                    if (cnn != null)
                    {
                        cnn.Close();
                    }
                }
            }
        }
    }
}
